package com.abel.bigwater.wflow.mapper

import com.abel.bigmeter.service.SearchRow
import com.abel.bigmeter.service.ZoneParam
import com.abel.bigwater.model.BwMeter
import com.abel.bigwater.model.DataRange
import com.abel.bigwater.model.zone.*
import org.apache.ibatis.annotations.*

@Mapper
interface ZoneMapper {
    /**
    <parameterMap type="map" id="zoneMap">
    <parameter property="firmId"/>
    <parameter property="zoneId"/>
    <parameter property="time"/>
    <parameter property="time1"/>
    <parameter property="time2"/>
    <parameter property="timeHour"/>
    <parameter property="timeWeekday"/>
    <parameter property="timeMonthday"/>
    <parameter property="timeMonth"/>
    <parameter property="timeYear"/>
    </parameterMap>
     */
    @Insert("""
		INSERT INTO bw.dm_zone(z_id
		, z_name
		, z_level
		, z_type
		, leaf_type
		, z_r
		, z_memo
		, default_ref
        , zone_loc
        , zone_region
		, firm_id)
		VALUES(#{id}
		, #{name}
		, #{level}
		, #{type}
		, #{leafType}
		, #{r}
		, #{memo}
		, #{defaultRef}
        , ST_GeomFromText(#{zoneLoc})
        , ST_GeomFromText(#{zoneRegion})
		, #{firmId});
        """)
    fun saveZone(zone: Zone): Int

    @Delete("""<script>
		DELETE FROM bw.dm_zone
		WHERE z_id = #{id}
		<if test="firmId != null">
			AND firm_id LIKE #{firmId}
		</if>
       </script>""")
    fun deleteZone(zone: Zone): Int

    @Update("""<script>
		UPDATE bw.dm_zone
        <set>
            <when test='name != null'>
                z_name = #{name},
            </when>
            <when test='level != null'>
                z_level = #{level},
            </when>
            <when test='type != null'>
                z_type = #{type},
            </when>
            <when test='leafType != null'>
                leaf_type = #{leafType},
            </when>
            <when test='r != null'>
                z_r = #{r},
            </when>
            <when test='memo != null'>
                z_memo = #{memo},
            </when>
            <when test='defaultRef != null'>
                default_ref = #{defaultRef},
            </when>
            <when test='zoneLoc != null'>
                zone_loc = ST_GeomFromText(#{zoneLoc}),
            </when>
            <when test='zoneRegion != null'>
                zone_region = ST_GeomFromText(#{zoneRegion})
            </when>
        </set>
		<where>
			<if test="id != null">
				z_id = #{id}
			</if>
			<if test="firmId != null">
				AND firm_id LIKE #{firmId}
			</if>
		</where>
       </script>""")
    fun updateZone(zone: Zone): Int

    @Update("""<script>
		UPDATE bw.dm_zone
        <set>
            <when test='zoneLoc != null'>
                zone_loc = ST_GeomFromText(#{zoneLoc}),
            </when>
            <when test='zoneRegion != null'>
                zone_region = ST_GeomFromText(#{zoneRegion})
            </when>
        </set>
		<where>
			<if test="id != null">
				z_id = #{id}
			</if>
			<if test="firmId != null">
				AND firm_id LIKE #{firmId}
			</if>
		</where>
       </script>""")
    fun updateZoneLoc(zone: Zone): Int

    /**
     * if test 中 and/or 必须小写。
     */
    @Select("""<script>
		SELECT z_id AS id
		, z_name AS name
		, z_level AS level
		, z_type AS type
		, leaf_type AS leafType
		, z_r AS r
       , big_meter_count AS bigMeterCount
       , resi_meter_count AS resiMeterCount
		, z_memo AS memo
		, default_ref AS defaultRef
		, firm_id AS firmId
        , ST_AsText(zone_loc) AS zoneLoc
        , ST_AsText(zone_region) AS zoneRegion
		FROM bw.dm_zone
		<where>
			<if test="firmId != null">
				firm_id LIKE #{firmId}
			</if>
			<if test="zoneId != null">
			 AND z_id LIKE #{zoneId}
			</if>
          <if test='zoneLevel != null and zoneLevel != 0'>
            AND z_level = #{zoneLevel}
          </if>
            <if test='keywords != null'>
                AND MATCH(z_id, z_name) AGAINST(#{keywords} IN BOOLEAN MODE)
            </if>
		</where>
		ORDER BY z_id
        </script>""")
    fun zoneList(zp: ZoneParam): List<Zone>

    /** 全文搜索 */
    @Select("""
        <script>
        SELECT firm_id AS firmId
        , z_id AS zoneId
        , CONCAT(z_id, ', ', z_name) AS matchedText
        , 'Zone' AS typeId
        , '片区' AS typeName
        FROM bw.dm_zone
        <where>
            <if test='firmId != null'>
                firm_id LIKE #{firmId}
            </if>
            <if test='keywords != null'>
                AND MATCH(z_id, z_name) AGAINST(#{keywords} IN BOOLEAN MODE);
            </if>
        </where>
        </script>
    """)
    fun searchZoneText(zp: ZoneParam): List<SearchRow>

    /**
     * only save/delete/list for zone-meter. no update here.
     */
    @Insert("""
		INSERT INTO bw.dm_zone_meter(z_id, meter_id, flow_out)
		VALUES(#{zoneId}, #{meterId}, #{flowOut});
        """)
    fun saveZoneMeter(zm: ZoneMeter): Int

    @Delete("""<script>
		DELETE FROM bw.dm_zone_meter
		WHERE z_id = #{zoneId}
		<if test="meterId != null">
			AND meter_id = #{meterId}
		</if>
        </script>""")
    fun deleteZoneMeter(zm: ZoneMeter): Int

    @Select("""<script>
		SELECT DISTINCT
       m.meter_id AS id,
       user_code AS userCode,
		ext_id AS extId,
		meter_name AS name, meter_location AS location, install_date AS installDate,
		q1, q2, q3, q4, q1r, q2r, q3r, q4r,
		meter_size AS size,
		meter_model AS model, meter_type_id AS typeId, is_charge AS isCharge,
		zm.flow_out AS inOutput,
		create_by AS createBy, create_date AS createDate, update_by AS updateBy,
		update_date AS updateDate,
		dma_id AS dmaId,
		meter_brand_id AS meterBrandId,
		remote_brand_id AS remoteBrandId,
		rtu_id AS rtuId,
		pressure_range AS pressureRange,
		pressure_max_limit AS pressureMaxLimit,
		pressure_min_limit AS pressureMinLimit,
		power_type AS powerType,
		meter_status AS meterStatus,
		admin_mobile AS adminMobile,
		last_calib AS lastCalib,
		ST_AsText(meter_loc) AS meterLoc,
		m.firm_id AS firmId
		FROM bw.dm_zone z
        JOIN bw.dm_zone_meter zm ON (z.z_id = zm.z_id)
		JOIN bw.bw_meter m ON (zm.meter_id = m.meter_id)
		WHERE z.z_id LIKE #{zoneId}
		AND z.firm_id = #{firmId}
        <when test='meterId != null'>
            AND m.meter_id LIKE #{meterId}
        </when>
        </script>""")
    fun zoneMeterList(zp: ZoneParam): List<BwMeter>

    @Select("""<script>
		SELECT DISTINCT
       m.meter_id AS id,
       user_code AS userCode,
		ext_id AS extId,
		meter_name AS name, meter_location AS location, install_date AS installDate,
		q1, q2, q3, q4, q1r, q2r, q3r, q4r,
		meter_size AS size,
		meter_model AS model, meter_type_id AS typeId, is_charge AS isCharge,
		zm.flow_out AS inOutput,
		create_by AS createBy, create_date AS createDate, update_by AS updateBy,
		update_date AS updateDate,
		dma_id AS dmaId,
		meter_brand_id AS meterBrandId,
		remote_brand_id AS remoteBrandId,
		rtu_id AS rtuId,
		pressure_range AS pressureRange,
		pressure_max_limit AS pressureMaxLimit,
		pressure_min_limit AS pressureMinLimit,
		power_type AS powerType,
		meter_status AS meterStatus,
		admin_mobile AS adminMobile,
		last_calib AS lastCalib,
		ST_AsText(meter_loc) AS meterLoc,
		m.firm_id AS firmId
		FROM bw.bw_meter m
        LEFT JOIN bw.dm_zone_meter zm ON (zm.meter_id = m.meter_id)
		WHERE m.firm_id = #{firmId}
        AND zm.z_id IS NULL
        <when test='meterId != null'>
            AND m.meter_id LIKE #{meterId}
        </when>
        </script>""")
    fun zoneNoneMeterList(zp: ZoneParam): List<BwMeter>

    @Select("""<script>
		SELECT z.z_id AS id
		, z_name AS name
		, z_level AS level
		, z_type AS type
		, leaf_type AS leafType
		, z_r AS r
       , big_meter_count AS bigMeterCount
       , resi_meter_count AS resiMeterCount
       , COUNT(zm.meter_id) onlineBigMeterCount
		, z_memo AS memo
		, default_ref AS defaultRef
		, firm_id AS firmId
		FROM bw.dm_zone z
        , bw.dm_zone_meter zm
		<where>
            zm.z_id LIKE CONCAT(z.z_id, '%')
			<if test="firmId != null">
				AND firm_id LIKE #{firmId}
			</if>
			<if test="zoneId != null">
			    AND z.z_id LIKE #{zoneId}
			</if>
            AND z.z_level &lt;= 2
		</where>
        GROUP BY z.z_id
		ORDER BY z.z_id
        </script>""")
    fun zoneMeterCount(zp: ZoneParam): List<Zone>

    /**
     * only save/delete/list for hour flow. no update - just delete & save.
     */
    @Insert("""
		INSERT INTO bw.dm_zone_hour_flow(z_id
		, z_time
		, duration_seconds
		, water_in, water_out
		, water_subtotal, water_measure, water_hist, water_temp
		, total_leakage, physical_leakage, measure_leakage, other_missing
		, reading, pulse_liter, finished
		, sum_method
		, warn_water, warn_index, should_fix
		, yest_hour
		, week_hour, week_hour_avg, week_hour_error
		, month_hour, month_hour_avg, month_hour_error
		, year_hour, year_hour_avg, year_hour_error
        , firm_id
        , total_meter_count, finished_meter_count, uploading_meter_count
        , total_sub_count, finished_sub_count, uploading_sub_count
		)
		VALUES(#{zoneId}
		, #{time}
		, #{durationSeconds}
		, #{waterIn}, #{waterOut}
		, #{waterSubtotal}, #{waterMeasure}, #{waterHist}, #{waterTemp}
		, #{totalLeakage}, #{physicalLeakage}, #{measureLeakage}, #{otherMissing}
        , #{reading}, #{pulseLiter}, #{finished}
		, #{sumMethod}
		, #{warnWater}, #{warnIndex}, #{shouldFix}
		, #{yestHour}
		, #{weekHour}, #{weekHourAvg}, #{weekHourError}
		, #{monthHour}, #{monthHourAvg}, #{monthHourError}
		, #{yearHour}, #{yearHourAvg}, #{yearHourError}
        , #{firmId}
        , #{totalMeterCount}, #{finishedMeterCount}, #{uploadingMeterCount}
        , #{totalSubCount}, #{finishedSubCount}, #{uploadingSubCount}
		);
        """)
    fun saveHourFlow(hf: ZoneHourFlow): Int

    @Delete("""<script>
	   DELETE zf FROM bw.dm_zone_hour_flow zf, dm_zone z
	   <where>
	       zf.z_id = z.z_id
	       <if test="zoneId != null">
	           AND zf.z_id = #{zoneId}
	       </if>
	       <if test="firmId != null">
	           AND z.firm_id LIKE #{firmId}
	       </if>
	       <if test="time != null">
	           AND z_time = #{time}
	       </if>
	       <if test="time1 != null">
	           AND z_time &gt;= #{time1}
	       </if>
	       <if test="time2 != null">
	           AND z_time &lt; #{time2}
	       </if>
	   </where>
        </script>""")
    fun deleteHourFlow(zp: ZoneParam): Int

    @Delete("""
       DELETE FROM bw.dm_zone_hour_flow
       WHERE z_id = #{zoneId}
       AND z_time = #{time}
        """)
    fun deleteHourFlowSingle(hf: ZoneHourFlow): Int

    /**
     * minTime, maxTime
     */
    @Select("""<script>
        SELECT z.z_id AS zoneId
        , MIN(z_time) AS minTime
        , MAX(z_time) AS maxTime
        FROM bw.dm_zone z
        JOIN bw.dm_zone_hour_flow zf ON (z.z_id = zf.z_id)
        <where>
            <if test="zoneId != null">
                z.z_id LIKE #{zoneId}
            </if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
            <if test="firmId != null">
                AND z.firm_id LIKE #{firmId}
            </if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
            <if test="time != null">
                AND z_time = #{time}
            </if>
            <if test="time1 != null">
                AND z_time &gt; #{time1}
            </if>
            <if test="time2 != null">
                AND z_time &lt;= #{time2}
            </if>
        </where>
        GROUP BY z.z_id
        </script>""")
    fun hourFlowRange(zp: ZoneParam): List<DataRange>

    @Select("""<script>
		SELECT z.z_id AS zoneId
		, z.z_name AS zoneName
		, z_time AS time
		, duration_seconds AS durationSeconds
		, water_in AS waterIn
		, water_out AS waterOut
		, water_subtotal AS waterSubtotal
		, water_measure AS waterMeasure
		, water_hist AS waterHist
		, water_temp AS waterTemp
		, total_leakage AS totalLeakage
		, physical_leakage AS physicalLeakage
		, measure_leakage AS measureLeakage
		, other_missing AS otherMissing
        , reading AS reading
        , pulse_liter AS pulseLiter
        , finished AS finished
		, sum_method AS sumMethod
		, warn_water AS warnWater
		, warn_index AS warnIndex
		, should_fix AS shouldFix
		, yest_hour AS yestHour
		, week_hour AS weekHour
		, week_hour_avg AS weekHourAvg
		, week_hour_error AS weekHourError
		, month_hour AS monthHour
		, month_hour_avg AS monthHourAvg
		, month_hour_error AS monthHourError
		, year_hour AS yearHour
		, year_hour_avg AS yearHourAvg
		, year_hour_error AS yearHourError
        , z.firm_id AS firmId
        , total_meter_count AS totalMeterCount
        , finished_meter_count AS finishedMeterCount
        , uploading_meter_count AS uploadingMeterCount
        , total_sub_count AS totalSubCount
        , finished_sub_count AS finishedSubCount
        , uploading_sub_count AS uploadingSubCount
        , St_asText(z.zone_loc) AS zoneLoc
		FROM bw.dm_zone z
        JOIN bw.dm_zone_hour_flow zf ON (z.z_id = zf.z_id)
		<where>
			<if test="zoneId != null">
				z.z_id LIKE #{zoneId}
			</if>
			<if test="firmId != null">
				AND z.firm_id LIKE #{firmId}
			</if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
			<if test="time != null">
				AND z_time = #{time}
			</if>
			<if test="time1 != null">
				AND z_time &gt;= #{time1}
			</if>
			<if test="time2 != null">
				AND z_time &lt; #{time2}
			</if>
			<if test="timeHour != null">
			    AND HOUR(z_time) = #{timeHour}
			</if>
			<if test="timeWeekday != null">
			     AND WEEKDAY(z_time) = #{timeWeekday} - 1
			</if>
			<if test="timeMonthday != null">
			     AND DAYOFMONTH(z_time) = #{timeMonthday}
			</if>
			<if test="timeMonth != null">
			     AND MONTH(z_time) = #{timeMonth}
			</if>
			<if test="timeYear != null">
			     AND YEAR(z_time) = #{timeYear}
			</if>
		</where>
		ORDER BY z.z_id, z_time
        </script>""")
    fun hourFlowList(zp: ZoneParam): List<ZoneHourFlow>

    @Select("""<script>
        SELECT z.z_id AS zoneId
        , z.z_name AS zoneName
        , z_time AS time
        , duration_seconds AS durationSeconds
        , water_in AS waterIn
        , water_out AS waterOut
        , water_subtotal AS waterSubtotal
        , water_measure AS waterMeasure
        , water_hist AS waterHist
        , water_temp AS waterTemp
        , total_leakage AS totalLeakage
        , physical_leakage AS physicalLeakage
        , measure_leakage AS measureLeakage
        , other_missing AS otherMissing
        , reading AS reading
        , pulse_liter AS pulseLiter
        , finished AS finished
        , sum_method AS sumMethod
        , warn_water AS warnWater
        , warn_index AS warnIndex
        , should_fix AS shouldFix
        , yest_hour AS yestHour
        , week_hour AS weekHour
        , week_hour_avg AS weekHourAvg
        , week_hour_error AS weekHourError
        , month_hour AS monthHour
        , month_hour_avg AS monthHourAvg
        , month_hour_error AS monthHourError
        , year_hour AS yearHour
        , year_hour_avg AS yearHourAvg
        , year_hour_error AS yearHourError
        , z.firm_id AS firmId
        , total_meter_count AS totalMeterCount
        , finished_meter_count AS finishedMeterCount
        , uploading_meter_count AS uploadingMeterCount
        , total_sub_count AS totalSubCount
        , finished_sub_count AS finishedSubCount
        , uploading_sub_count AS uploadingSubCount
        FROM bw.dm_zone z
        JOIN bw.dm_zone_hour_flow zf ON (z.z_id = zf.z_id)
        <where>
            <if test="zoneId != null">
                z.z_id LIKE #{zoneId}
            </if>
            <if test="firmId != null">
                AND z.firm_id LIKE #{firmId}
            </if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
            <if test="time != null">
                AND z_time = #{time}
            </if>
            <if test="time1 != null">
                AND z_time &gt;= #{time1}
            </if>
            <if test="time2 != null">
                AND z_time &lt; #{time2}
            </if>
            <if test="timeHour != null">
                AND HOUR(z_time) = #{timeHour}
            </if>
            <if test="timeWeekday != null">
                 AND WEEKDAY(z_time) = #{timeWeekday} - 1
            </if>
            <if test="timeMonthday != null">
                 AND DAYOFMONTH(z_time) = #{timeMonthday}
            </if>
            <if test="timeMonth != null">
                 AND MONTH(z_time) = #{timeMonth}
            </if>
            <if test="timeYear != null">
                 AND YEAR(z_time) = #{timeYear}
            </if>
        </where>
        ORDER BY z.z_id, z_time
        LIMIT 0,1
        </script>""")
    fun hourFlow(zp: ZoneParam): ZoneHourFlow

    /**
     * only save/delete/list for day flow. no update - just delete & save.
     */
    @Insert("""<script>
		INSERT INTO bw.dm_zone_day_flow(z_id
		, z_time
		, duration_seconds
		, water_in, water_out
		, water_subtotal, water_measure, water_hist, water_temp
		, total_leakage, physical_leakage, measure_leakage, other_missing
        , reading, pulse_liter, finished
		, sum_method
		, warn_water, warn_index, should_fix
		, min_flow, avg_flow, max_flow
		, yest_water, yest_min
		, week_day, week_avg, week_error
		, week_day_min, week_min_avg, week_min_error
		, month_day, month_avg, month_error
		, month_day_min, month_min_avg, month_min_error
		, year_day, year_month_avg, year_month_error
		, year_month_min, year_month_min_avg, year_month_min_error
        , firm_id
        , total_meter_count, finished_meter_count, uploading_meter_count
        , total_sub_count, finished_sub_count, uploading_sub_count
		)
		VALUES(#{zoneId}
		, #{time}
		, #{durationSeconds}
		, #{waterIn}, #{waterOut}
		, #{waterSubtotal}, #{waterMeasure}, #{waterHist}, #{waterTemp}
		, #{totalLeakage}, #{physicalLeakage}, #{measureLeakage}, #{otherMissing}
        , #{reading}, #{pulseLiter}, #{finished}
		, #{sumMethod}
		, #{warnWater}, #{warnIndex}, #{shouldFix}
		, #{minFlow}, #{avgFlow}, #{maxFlow}
		, #{yestWater}, #{yestMin}
		, #{weekDay}, #{weekAvg}, #{weekError}
		, #{weekDayMin}, #{weekMinAvg}, #{weekMinError}
		, #{monthDay}, #{monthAvg}, #{monthError}
		, #{monthDayMin}, #{monthMinAvg}, #{monthMinError}
		, #{yearDay}, #{yearMonthAvg}, #{yearMonthError}
		, #{yearMonthMin}, #{yearMonthMinAvg}, #{yearMonthMinError}
        , #{firmId}
        , #{totalMeterCount}, #{finishedMeterCount}, #{uploadingMeterCount}
        , #{totalSubCount}, #{finishedSubCount}, #{uploadingSubCount}
		);
        </script>""")
    fun saveDayFlow(df: ZoneDayFlow): Int

    @Delete("""<script>
       DELETE zf FROM bw.dm_zone_day_flow zf, bw.dm_zone z
       <where>
            zf.z_id = z.z_id
           <if test="zoneId != null">
               AND zf.z_id = #{zoneId}
           </if>
           <if test="firmId != null">
                AND z.firm_id LIKE #{firmId}
           </if>
           <if test="time != null">
               AND z_time = #{time}
           </if>
           <if test="time1 != null">
               AND z_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND z_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun deleteDayFlow(zp: ZoneParam): Int

    @Delete("""<script>
       DELETE FROM bw.dm_zone_day_flow
       WHERE z_id = #{zoneId}
       AND z_time = #{time}
        </script>""")
    fun deleteDayFlowSingle(df: ZoneDayFlow): Int

    /**
     * minTime, maxTime
     */
    @Select("""<script>
        SELECT z.z_id AS zoneId
        , MIN(z_time) AS minTime
        , MAX(z_time) AS maxTime
        FROM bw.dm_zone z
        JOIN bw.dm_zone_day_flow zf ON (z.z_id = zf.z_id)
        <where>
            <if test="zoneId != null">
                z.z_id LIKE #{zoneId}
            </if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
            <if test="firmId != null">
                AND z.firm_id LIKE #{firmId}
            </if>
            <if test="time != null">
                AND z_time = #{time}
            </if>
            <if test="time1 != null">
                AND z_time &gt; #{time1}
            </if>
            <if test="time2 != null">
                AND z_time &lt;= #{time2}
            </if>
        </where>
        GROUP BY z.z_id
        </script>""")
    fun dayFlowRange(zp: ZoneParam): List<DataRange>

    @Select("""<script>
		SELECT z.z_id AS zoneId
		, z.z_name AS zoneName
		, z_time AS time
		, duration_seconds AS durationSeconds
		, water_in AS waterIn
		, water_out AS waterOut
		, water_subtotal AS waterSubtotal
		, water_measure AS waterMeasure
		, water_hist AS waterHist
		, water_temp AS waterTemp
		, total_leakage AS totalLeakage
		, physical_leakage AS physicalLeakage
		, measure_leakage AS measureLeakage
		, other_missing AS otherMissing
        , reading AS reading
        , pulse_liter AS pulseLiter
        , finished AS finished
		, sum_method AS sumMethod
		, warn_water AS warnWater
		, warn_index AS warnIndex
		, should_fix AS shouldFix
		, min_flow AS minFlow
		, avg_flow AS avgFlow, max_flow AS maxFlow
		, yest_water AS yestWater, yest_min AS yestMin
		, week_day AS weekDay, week_avg AS weekAvg, week_error AS weekError
		, week_day_min AS weekDayMin, week_min_avg AS weekMinAvg, week_min_error AS weekMinError
		, month_day AS monthDay, month_avg AS monthAvg, month_error AS monthError
		, month_day_min AS monthDayMin, month_min_avg AS monthMinAvg, month_min_error AS monthMinError
		, year_day AS yearDay, year_month_avg AS yearMonthAvg, year_month_error AS yearMonthError
		, year_month_min AS yearMonthMin, year_month_min_avg AS yearMonthMinAvg, year_month_min_error AS yearMonthMinError
        , St_asText(z.zone_loc) AS zoneLoc
        , z.firm_id AS firmId
        , total_meter_count AS totalMeterCount
        , finished_meter_count AS finishedMeterCount
        , uploading_meter_count AS uploadingMeterCount
        , total_sub_count AS totalSubCount
        , finished_sub_count AS finishedSubCount
        , uploading_sub_count AS uploadingSubCount
		FROM bw.dm_zone z
        JOIN bw.dm_zone_day_flow zf ON (z.z_id = zf.z_id)
		<where>
			<if test="zoneId != null">
				z.z_id LIKE #{zoneId}
			</if>
			<if test="firmId != null">
				AND z.firm_id LIKE #{firmId}
			</if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
			<if test="time != null">
				AND z_time = #{time}
			</if>
			<if test="time1 != null">
				AND z_time &gt;= #{time1}
			</if>
			<if test="time2 != null">
				AND z_time &lt; #{time2}
			</if>
            <if test="timeWeekday != null">
                 AND WEEKDAY(z_time) = #{timeWeekday} - 1
            </if>
            <if test="timeMonthday != null">
                 AND DAYOFMONTH(z_time) = #{timeMonthday}
            </if>
            <if test="timeMonth != null">
                AND MONTH(z_time) = #{timeMonth}
            </if>
            <if test="timeYear != null">
                 AND YEAR(z_time) = #{timeYear}
            </if>
		</where>
		ORDER BY z.z_id, z_time
        </script>""")
    fun dayFlowList(zp: ZoneParam): List<ZoneDayFlow>

    @Select("""<script>
        SELECT z.z_id AS zoneId
        , z.z_name AS zoneName
        , z_time AS time
        , duration_seconds AS durationSeconds
        , water_in AS waterIn
        , water_out AS waterOut
        , water_subtotal AS waterSubtotal
        , water_measure AS waterMeasure
        , water_hist AS waterHist
        , water_temp AS waterTemp
        , total_leakage AS totalLeakage
        , physical_leakage AS physicalLeakage
        , measure_leakage AS measureLeakage
        , other_missing AS otherMissing
        , reading AS reading
        , pulse_liter AS pulseLiter
        , finished AS finished
        , sum_method AS sumMethod
        , warn_water AS warnWater
        , warn_index AS warnIndex
        , should_fix AS shouldFix
        , min_flow AS minFlow
        , avg_flow AS avgFlow, max_flow AS maxFlow
        , yest_water AS yestWater, yest_min AS yestMin
        , week_day AS weekDay, week_avg AS weekAvg, week_error AS weekError
        , week_day_min AS weekDayMin, week_min_avg AS weekMinAvg, week_min_error AS weekMinError
        , month_day AS monthDay, month_avg AS monthAvg, month_error AS monthError
        , month_day_min AS monthDayMin, month_min_avg AS monthMinAvg, month_min_error AS monthMinError
        , year_day AS yearDay, year_month_avg AS yearMonthAvg, year_month_error AS yearMonthError
        , year_month_min AS yearMonthMin, year_month_min_avg AS yearMonthMinAvg, year_month_min_error AS yearMonthMinError
        , z.firm_id AS firmId
        , total_meter_count AS totalMeterCount
        , finished_meter_count AS finishedMeterCount
        , uploading_meter_count AS uploadingMeterCount
        , total_sub_count AS totalSubCount
        , finished_sub_count AS finishedSubCount
        , uploading_sub_count AS uploadingSubCount
        FROM bw.dm_zone z
        JOIN bw.dm_zone_day_flow zf ON (z.z_id = zf.z_id)
        <where>
            <if test="zoneId != null">
                z.z_id LIKE #{zoneId}
            </if>
            <if test="firmId != null">
                AND z.firm_id LIKE #{firmId}
            </if>
            <if test="zoneLevel != null">
                AND z.z_level = #{zoneLevel}
            </if>
            <if test="time != null">
                AND z_time = #{time}
            </if>
            <if test="time1 != null">
                AND z_time &gt;= #{time1}
            </if>
            <if test="time2 != null">
                AND z_time &lt; #{time2}
            </if>
            <if test="timeWeekday != null">
                 AND WEEKDAY(z_time) = #{timeWeekday} - 1
            </if>
            <if test="timeMonthday != null">
                 AND DAYOFMONTH(z_time) = #{timeMonthday}
            </if>
            <if test="timeMonth != null">
                AND MONTH(z_time) = #{timeMonth}
            </if>
            <if test="timeYear != null">
                 AND YEAR(z_time) = #{timeYear}
            </if>
        </where>
        ORDER BY z.z_id, z_time
        LIMIT 0,1
        </script>""")
    fun dayFlow(zp: ZoneParam): ZoneDayFlow

    /**
     * save/delete/update/list for zone template.
    <selectKey keyProperty="id" resultType="long" order="AFTER">
    SELECT LAST_INSERT_ID() = #{id
    </selectKey>
     */
    @Insert("""<script>
		INSERT INTO bw.dm_zone_template(z_id
		, ref_type
		, `day_hour`, week_day, month_day, `year_month`
		, water_consume
        , total_leakage, physical_leakage, measure_leakage, other_missing
        , min_flow, avg_flow, max_flow
		, temp_memo)
		VALUES(#{zoneId}
		, #{refType}
		, #{dayHour}, #{weekDay}, #{monthDay}, #{yearMonth}
		, #{waterConsume}
        , #{totalLeakage}, #{physicalLeakage}, #{measureLeakage}, #{otherMissing}
        , #{minFlow}, #{avgFlow}, #{maxFlow}
		, #{memo});
        </script>""")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "z_id")
    fun saveZoneTemp(zt: ZoneTemplate): Int

    @Delete("""<script>
		DELETE FROM bw.dm_zone_template
		<where>
			<if test="id > 0">
				temp_id = #{id}
			</if>
			<if test="zoneId != null">
				AND z_id = #{zoneId}
			</if>
			<if test="refType != null">
				AND ref_type = #{refType}
			</if>
		</where>
        </script>""")
    fun deleteZoneTemp(zt: ZoneTemplate): Int

    @Update("""
		UPDATE bw.dm_zone_template
		SET z_id = #{zoneId}
		, ref_type = #{refType}
		, `day_hour` = #{dayHour}
		, week_day = #{weekDay}
		, month_day = #{monthDay}
		, `year_month` = #{yearMonth}
		, water_consume = #{waterConsume}
        , total_leakage = #{totalLeakage}
        , physical_leakage = #{physicalLeakage}
        , measure_leakage = #{measureLeakage}
        , other_missing = #{otherMissing}
        , min_flow = #{minFlow}
        , avg_flow = #{avgFlow}, max_flow = #{maxFlow}
		, temp_memo = #{memo}
		WHERE temp_id = #{id}
        """)
    fun updateZoneTemp(zt: ZoneTemplate): Int

    @Select("""<script>
		SELECT temp_id = #{id
		, z_id = #{zoneId
		, ref_type = #{refType
		, `day_hour` = #{dayHour
		, week_day = #{weekDay
		, month_day = #{monthDay
		, `year_month` = #{yearMonth
		, water_consume = #{waterConsume
        , total_leakage = #{totalLeakage
        , physical_leakage = #{physicalLeakage
        , measure_leakage = #{measureLeakage
        , other_missing = #{otherMissing
        , min_flow = #{minFlow
        , avg_flow = #{avgFlow, max_flow = #{maxFlow
		, temp_memo = #{memo
		FROM bw.dm_zone_template
		<where>
			<if test="zoneId != null">
				z_id LIKE #{zoneId}
			</if>
			<if test="ref_type != null">
				AND ref_type = #{refType}
			</if>
		</where>
		ORDER BY ref_type, `day_hour`, week_day, month_day, `year_month`
        </script>""")
    fun zoneTempList(zp: ZoneParam): List<ZoneTemplate>

    /**
     * save/delete/list for meter hour flow.
     */
    @Insert("""<script>
	   INSERT INTO bw.dm_meter_hour_flow(meter_id
	   , firm_id
	   , meter_time
	   , meter_flow
	   , flow_measure
       , pressure_avg
	   , flow_min
	   , flow_max
	   , pressure_min
	   , pressure_max
	   , pressure_reading_min
	   , pressure_reading_max
	   , flow_hist
	   , flow_temp
	   , meter_volume, meter_duration
	   , start_reading, start_time
	   , end_reading, end_time
	   , pulse_liter
       , yest_hour
       , week_hour, week_hour_avg, week_hour_error
       , month_hour, month_hour_avg, month_hour_error
       , year_hour, year_hour_avg, year_hour_error)
       VALUES(#{meterId}
       , #{firmId}
       , #{time}
       , #{flow}
       , #{measureFlow}
       , #{pressureAvg}
       , #{flowMin}
       , #{flowMax}
       , #{pressureMin}
       , #{pressureMax}
       , #{pressureReadingMin}
       , #{pressureReadingMax}
       , #{histFlow}
       , #{tempFlow}
       , #{volume}, #{duration}
       , #{startReading}, #{startTime}
       , #{endReading}, #{endTime}
       , #{pulseLiter}
       , #{yestHour}
       , #{weekHour}, #{weekHourAvg}, #{weekHourError}
       , #{monthHour}, #{monthHourAvg}, #{monthHourError}
       , #{yearHour}, #{yearHourAvg}, #{yearHourError}
       );
        </script>""")
    fun saveMeterHourFlow(mhf: MeterHourFlow): Int

    @Update("""<script>
	   UPDATE bw.dm_meter_hour_flow
	   SET meter_flow = #{flow}
       , flow_measure = #{measureFlow}
       , pressure_avg = #{pressureAvg}
       , flow_min = #{flowMin}
       , flow_max = #{flowMax}
       , pressure_min = #{pressureMin}
       , pressure_max = #{pressureMax}
       , pressure_reading_min = #{pressureReadingMin}
       , pressure_reading_max = #{pressureReadingMax}
       , flow_hist = #{histFlow}
       , flow_temp = #{tempFlow}

       , meter_volume = #{volume}
       , meter_duration = #{duration}

       , start_reading = #{startReading}
       , start_time = #{startTime}
       , end_reading = #{endReading}
       , end_time = #{endTime}
       , pulse_liter = #{pulseLiter}

       , yest_hour = #{yestHour}
       , week_hour = #{weekHour}
       , week_hour_avg = #{weekHourAvg}
       , week_hour_error = #{weekHourError}

       , month_hour = #{monthHour}
       , month_hour_avg = #{monthHourAvg}
       , month_hour_error = #{monthHourError}
       , year_hour = #{yearHour}
       , year_hour_avg = #{yearHourAvg}
       , year_hour_error = #{yearHourError}
       <where>
           meter_id = #{meterId}
           AND meter_time = #{time}
           <if test="firmId != null">
               AND firm_id = #{firmId}
           </if>
       </where>
        </script>""")
    fun updateMeterHourFlow(mhf: MeterHourFlow): Int

    @Delete("""<script>
       DELETE zf FROM bw.dm_meter_hour_flow zf
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun deleteMeterHourFlow(zp: ZoneParam): Int

    @Select("""<script>
        SELECT zf.meter_id AS meterId
        , m.meter_name AS meterName
        , zf.firm_id AS firmId
        , zf.meter_time AS time
        , zf.meter_flow AS flow
        , zf.flow_measure AS measureFlow
        , zf.pressure_avg AS pressureAvg
        , zf.flow_min AS flowMin
        , zf.flow_max AS flowMax
        , zf.pressure_min AS pressureMin
        , zf.pressure_max AS pressureMax
        , zf.pressure_reading_min AS pressureReadingMin
        , zf.pressure_reading_max AS pressureReadingMax
        , zf.flow_hist AS histFlow
        , zf.flow_temp AS tempFlow
        , zf.meter_volume AS volume
        , zf.meter_duration AS duration
        , zf.start_reading AS startReading
        , zf.start_time AS startTime
        , zf.end_reading AS endReading
        , zf.end_time AS endTime
        , zf.pulse_liter AS pulseLiter
        , zf.yest_hour AS yestHour
        , zf.week_hour AS weekHour
        , zf.week_hour_avg AS weekHourAvg
        , zf.week_hour_error AS weekHourError
        , zf.month_hour AS monthHour
        , zf.month_hour_avg AS monthHourAvg
        , zf.month_hour_error AS monthHourError
        , zf.year_hour AS yearHour
        , zf.year_hour_avg AS yearHourAvg
        , zf.year_hour_error AS yearHourError
        FROM bw.bw_meter m
        JOIN bw.dm_meter_hour_flow zf ON (m.meter_id = zf.meter_id)
        <where>
            <if test="meterId != null">
                zf.meter_id LIKE #{meterId}
            </if>
            <if test="firmId != null">
                AND zf.firm_id LIKE #{firmId}
            </if>
            <if test="time != null">
                AND meter_time = #{time}
            </if>
            <if test="time1 != null">
                AND meter_time &gt;= #{time1}
            </if>
            <if test="time2 != null">
                AND meter_time &lt; #{time2}
            </if>
            <if test="timeHour != null">
                AND HOUR(meter_time) = #{timeHour}
            </if>
            <if test="timeWeekday != null">
                 AND WEEKDAY(meter_time) = #{timeWeekday} - 1
            </if>
            <if test="timeMonthday != null">
                 AND DAYOFMONTH(meter_time) = #{timeMonthday}
            </if>
            <if test="timeMonth != null">
                 AND MONTH(meter_time) = #{timeMonth}
            </if>
            <if test="timeYear != null">
                 AND YEAR(meter_time) = #{timeYear}
            </if>
        </where>
        ORDER BY zf.meter_id, zf.meter_time
        </script>""")
    fun meterHourFlowList(zp: ZoneParam): List<MeterHourFlow>

    /**
     * save/delete/list for meter template
    <selectKey keyProperty="id" order="AFTER" resultType="long">
    SELECT LAST_INSERT_ID() = #{id
    </selectKey>
     */
    @Insert("""<script>
        INSERT INTO bw.dm_meter_template(meter_id
        , firm_id
        , ref_type
        , `day_hour`, week_day, month_day, `year_month`
        , min_flow, avg_flow, max_flow
        , temp_memo)
        VALUES(#{meterId}
        , #{firmId}
        , #{refType}
        , #{dayHour}, #{weekDay}, #{monthDay}, #{yearMonth}
        , #{minFlow}, #{avgFlow}, #{maxFlow}
        , #{memo}
        )
        </script>""")
    @Options(useGeneratedKeys = true, keyColumn = "temp_id", keyProperty = "id")
    fun saveMeterTemplate(mt: MeterTemplate): Int

    @Delete("""<script>
        DELETE FROM bw.dm_meter_template
        <where>
            firm_id = #{firmId}
            <if test="id > 0">
                AND temp_id = #{id}
            </if>
            <if test="meterId != null">
                AND meter_id = #{meterId}
            </if>
            <if test="refType != null">
                AND ref_type = #{refType}
            </if>
        </where>
        </script>""")
    fun deleteMeterTemp(mt: MeterTemplate): Int

    @Select("""<script>
        SELECT temp_id AS id
        , meter_id AS meterId
        , ref_type AS refType
        , `day_hour` AS dayHour
        , week_day AS weekDay
        , month_day AS monthDay
        , `year_month` AS yearMonth
        , min_flow AS minFlow
        , avg_flow AS avgFlow
        , max_flow AS maxFlow
        , p_min AS minp
        , p_avg AS avgp
        , p_max AS maxp
        , p_reading_min AS minpReading
        , p_reading_max AS maxpReading
        , temp_memo AS memo
        , update_time AS updateTime
        FROM bw.dm_meter_template
        <where>
            <if test="firmId != null">
                firm_id = #{firmId}
            </if>
            <if test="meterId != null">
                AND meter_id LIKE #{meterId}
            </if>
            <if test="ref_type != null">
                AND ref_type = #{refType}
            </if>
        </where>
        ORDER BY meter_id, ref_type, `day_hour`, week_day, month_day, `year_month`
        </script>""")
    fun meterTempList(zp: ZoneParam): List<MeterTemplate>

    @Select("""<script>
        SELECT m.firm_id AS firmId
, m.meter_id AS meterId
, m.meter_name AS meterName
, meter_time AS `time`
, meter_flow AS flow
, flow_measure AS measureFlow
, pressure_avg AS pressureAvg
, flow_min AS flowMin
, flow_max AS flowMax
, pressure_min AS pressureMin
, pressure_max AS pressureMax
, pressure_reading_min AS pressureReadingMin
, pressure_reading_max AS pressureReadingMax
, flow_hist AS histFlow
, flow_temp AS tempFlow
, meter_volume AS volume
, meter_duration AS duration
, start_reading AS startReading
, start_time AS startTime
, end_reading AS endReading
, end_time AS endTime
, pulse_liter AS pulseLiter
, yest_hour AS yestHour
, week_hour AS weekHour
, week_hour_avg AS weekHourAvg
, week_hour_error AS weekHourError
, month_hour AS monthHour
, month_hour_avg AS monthHourAvg
, month_hour_error AS monthHourError
, year_hour AS yearHour
, year_hour_avg AS yearHourAvg
, year_hour_error AS yearHourError
        FROM bw.dm_meter_hour_flow zf
        RIGHT JOIN bw.bw_meter m ON zf.meter_id = m.meter_id
       <where>
           m.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND m.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun listMeterHourFlow(zp: ZoneParam): List<MeterHourFlow>


    /**
     * save/delete/list for meter hour flow.
     */
    @Insert("""<script>
	   INSERT INTO bw.dm_meter_day_flow(meter_id
	   , firm_id
	   , meter_time
	   , meter_flow
	   , flow_measure
       , pressure_avg
	   , flow_min
	   , flow_max
	   , pressure_min
	   , pressure_max
	   , pressure_reading_min
	   , pressure_reading_max
	   , flow_hist
	   , flow_temp
	   , meter_volume, meter_duration
	   , start_reading, start_time
	   , end_reading, end_time
	   , pulse_liter

, yest_water
, `yest_min`

, `week_day`
, `week_avg`
, `week_error`
, `week_day_min`
, `week_min_avg`
, `week_min_error`

, `month_day`
, `month_avg`
, `month_error`
, `month_day_min`
, `month_min_avg`
, `month_min_error`

, `year_day`
, `year_month_avg`
, `year_month_error`
, `year_month_min`
, `year_month_min_avg`
, `year_month_min_error`

, `update_time`)
       VALUES(#{meterId}
       , #{firmId}
       , #{time}
       , #{flow}
       , #{measureFlow}
       , #{pressureAvg}
       , #{flowMin}
       , #{flowMax}
       , #{pressureMin}
       , #{pressureMax}
       , #{pressureReadingMin}
       , #{pressureReadingMax}
       , #{histFlow}
       , #{tempFlow}
       , #{volume}, #{duration}
       , #{startReading}, #{startTime}
       , #{endReading}, #{endTime}
       , #{pulseLiter}

       , #{yestWater}
       , #{yestMin}

       , #{weekDay}
       , #{weekAvg}
       , #{weekError}
       , #{weekDayMin}
       , #{weekMinAvg}
       , #{weekMinError}

       , #{monthDay}
       , #{monthAvg}
       , #{monthError}
       , #{monthDayMin}
       , #{monthMinAvg}
       , #{monthMinError}

       , #{yearDay}
       , #{yearMonthAvg}
       , #{yearMonthError}
       , #{yearMonthMin}
       , #{yearMonthMinAvg}
       , #{yearMonthMinError}

       , #{updateTime}
       );
        </script>""")
    fun saveMeterDayFlow(mhf: MeterDayFlow): Int

    @Update("""<script>
	   UPDATE bw.dm_meter_day_flow
	   SET meter_flow = #{flow}
       , flow_measure = #{measureFlow}
       , pressure_avg = #{pressureAvg}
       , flow_min = #{flowMin}
       , flow_max = #{flowMax}
       , pressure_min = #{pressureMin}
       , pressure_max = #{pressureMax}
       , pressure_reading_min = #{pressureReadingMin}
       , pressure_reading_max = #{pressureReadingMax}
       , flow_hist = #{histFlow}
       , flow_temp = #{tempFlow}

       , meter_volume = #{volume}
       , meter_duration = #{duration}

       , start_reading = #{startReading}
       , start_time = #{startTime}
       , end_reading = #{endReading}
       , end_time = #{endTime}
       , pulse_liter = #{pulseLiter}

, yest_water = #{yestWater}
, `yest_min` = #{yestMin}
, `week_day` = #{weekDay}
, `week_avg` = #{weekAvg}
, `week_error` = #{weekError}
, `week_day_min` = #{weekDayMin}
, `week_min_avg` = #{weekMinAvg}
, `week_min_error` = #{weekMinError}
, `month_day` = #{monthDay}
, `month_avg` = #{monthAvg}
, `month_error` = #{monthError}
, `month_day_min` = #{monthDayMin}
, `month_min_avg` = #{monthMinAvg}
, `month_min_error` = #{monthMinError}
, `year_day` = #{yearDay}
, `year_month_avg` = #{yearMonthAvg}
, `year_month_error` = #{yearMonthError}
, `year_month_min` = #{yearMonthMin}
, `year_month_min_avg` = #{yearMonthMinAvg}
, `year_month_min_error` = #{yearMonthMinError}
, `update_time` = #{updateTime}
       <where>
           meter_id = #{meterId}
           AND meter_time = #{time}
           <if test="firmId != null">
               AND firm_id = #{firmId}
           </if>
       </where>
        </script>""")
    fun updateMeterDayFlow(mhf: MeterDayFlow): Int

    @Delete("""<script>
       DELETE zf FROM bw.dm_meter_day_flow zf
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun deleteMeterDayFlow(zp: ZoneParam): Int

    @Select("""<script>
        SELECT m.firm_id AS firmId
, m.meter_id AS meterId
, m.meter_name AS meterName
, meter_time AS `time`
, meter_flow AS flow
, flow_measure AS measureFlow
, pressure_avg AS pressureAvg
, flow_min AS flowMin
, flow_max AS flowMax
, pressure_min AS pressureMin
, pressure_max AS pressureMax
, pressure_reading_min AS pressureReadingMin
, pressure_reading_max AS pressureReadingMax
, flow_hist AS histFlow
, flow_temp AS tempFlow
, meter_volume AS volume
, meter_duration AS duration
, start_reading AS startReading
, start_time AS startTime
, end_reading AS endReading
, end_time AS endTime
, pulse_liter AS pulseLiter

, yest_water AS yestWater
, `yest_min` AS yestMin
, `week_day` AS weekDay
, `week_avg` AS weekAvg
, `week_error` AS weekError
, `week_day_min` AS weekDayMin
, `week_min_avg` AS weekMinAvg
, `week_min_error` AS weekMinError
, `month_day` AS monthDay
, `month_avg` AS monthAvg
, `month_error` AS monthError
, `month_day_min` AS monthDayMin
, `month_min_avg` AS monthMinAvg
, `month_min_error` AS monthMinError
, `year_day` AS yearDay
, `year_month_avg` AS yearMonthAvg
, `year_month_error` AS yearMonthError
, `year_month_min` AS yearMonthMin
, `year_month_min_avg` AS yearMonthMinAvg
, `year_month_min_error` AS yearMonthMinError
, `update_time` AS updateTime
        FROM bw.dm_meter_day_flow zf
        RIGHT JOIN bw.bw_meter m ON zf.meter_id = m.meter_id
       <where>
           m.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND m.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun listMeterDayFlow(zp: ZoneParam): List<MeterDayFlow>

    @Select("""<script>
        SELECT m.firm_id AS firmId
, m.meter_id AS meterId
, m.meter_name AS meterName
, MIN(meter_time) AS `time`
, AVG(pressure_avg) AS pressureAvg
, MIN(flow_min) AS flowMin
, MAX(flow_max) AS flowMax
, MIN(pressure_min) AS pressureMin
, MAX(pressure_max) AS pressureMax
, MIN(pressure_reading_min) AS pressureReadingMin
, MAX(pressure_reading_max) AS pressureReadingMax
, SUM(meter_volume) AS volume
, SUM(meter_duration) AS duration
, MIN(start_reading) AS startReading
, MIN(start_time) AS startTime
, MAX(end_reading) AS endReading
, MAX(end_time) AS endTime
, pulse_liter AS pulseLiter
, SUM(meter_volume) AS volume
, AVG(meter_volume) AS weekDailyAvg
, MIN(meter_volume) AS weekDailyMin
, MAX(meter_volume) AS weekDailyMax
, YEAR(meter_time) * 100 + WEEK(meter_time) AS yearWeek
        FROM bw.dm_meter_day_flow zf
        RIGHT JOIN bw.bw_meter m ON zf.meter_id = m.meter_id
       <where>
           m.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND m.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        GROUP BY m.meter_id, yearWeek
        </script>""")
    fun listMeterWeekFlow(zp: ZoneParam): List<MeterWeekFlow>

    @Select("""<script>
        SELECT m.firm_id AS firmId
, m.meter_id AS meterId
, m.meter_name AS meterName
, MIN(meter_time) AS `time`
, AVG(pressure_avg) AS pressureAvg
, MIN(flow_min) AS flowMin
, MAX(flow_max) AS flowMax
, MIN(pressure_min) AS pressureMin
, MAX(pressure_max) AS pressureMax
, MIN(pressure_reading_min) AS pressureReadingMin
, MAX(pressure_reading_max) AS pressureReadingMax
, SUM(meter_volume) AS volume
, SUM(meter_duration) AS duration
, MIN(start_reading) AS startReading
, MIN(start_time) AS startTime
, MAX(end_reading) AS endReading
, MAX(end_time) AS endTime
, pulse_liter AS pulseLiter
, SUM(meter_volume) AS volume
, AVG(meter_volume) AS monthDailyAvg
, MIN(meter_volume) AS monthDailyMin
, MAX(meter_volume) AS monthDailyMax
, YEAR(meter_time) * 100 + MONTH(meter_time) AS yearMonth
        FROM bw.dm_meter_day_flow zf
        RIGHT JOIN bw.bw_meter m ON zf.meter_id = m.meter_id
       <where>
           m.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND m.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        GROUP BY m.meter_id, yearMonth
        </script>""")
    fun listMeterMonthFlow(zp: ZoneParam): List<MeterMonthFlow>

    @Select("""<script>
 SELECT min(meter_time) AS minTime
 , max(meter_time) AS maxTime
 , meter_id AS meterId
        FROM bw.dm_meter_hour_flow zf
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
       GROUP BY meter_id
        </script>""")
    fun meterHourFlowRange(zp:ZoneParam): List<DataRange>?

    @Select("""<script>
 SELECT min(meter_time) AS minTime
 , max(meter_time) AS maxTime
 , meter_id AS meterId
        FROM bw.dm_meter_day_flow zf
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
       GROUP BY meter_id
        </script>""")
    fun meterDayFlowRange(zp:ZoneParam): List<DataRange>?

    @Select("""<script>
        SELECT zf.firm_id AS firmId
, mtr.meter_id AS meterId
, mtr.meter_name AS meterName
, ST_AsText(mtr.meter_loc) AS meterLoc
, meter_time AS `time`
, meter_flow AS flow
, flow_measure AS measureFlow
, pressure_avg AS pressureAvg
, flow_min AS flowMin
, flow_max AS flowMax
, pressure_min AS pressureMin
, pressure_max AS pressureMax
, pressure_reading_min AS pressureReadingMin
, pressure_reading_max AS pressureReadingMax
, flow_hist AS histFlow
, flow_temp AS tempFlow
, meter_volume AS volume
, meter_duration AS duration
, start_reading AS startReading
, start_time AS startTime
, end_reading AS endReading
, end_time AS endTime
, pulse_liter AS pulseLiter

, yest_water AS yestWater
, `yest_min` AS yestMin
, `week_day` AS weekDay
, `week_avg` AS weekAvg
, `week_error` AS weekError
, `week_day_min` AS weekDayMin
, `week_min_avg` AS weekMinAvg
, `week_min_error` AS weekMinError
, `month_day` AS monthDay
, `month_avg` AS monthAvg
, `month_error` AS monthError
, `month_day_min` AS monthDayMin
, `month_min_avg` AS monthMinAvg
, `month_min_error` AS monthMinError
, `year_day` AS yearDay
, `year_month_avg` AS yearMonthAvg
, `year_month_error` AS yearMonthError
, `year_month_min` AS yearMonthMin
, `year_month_min_avg` AS yearMonthMinAvg
, `year_month_min_error` AS yearMonthMinError
, `update_time` AS updateTime
        FROM bw.dm_meter_day_flow zf
        JOIN (
        SELECT meter_id AS meterId
        , MAX(meter_time) meterTime
        FROM bw.dm_meter_day_flow
        GROUP BY meter_id) zfm ON zf.meter_id = zfm.meterId AND zf.meter_time = zfm.meterTime
        LEFT JOIN bw.bw_meter mtr ON zf.meter_id = mtr.meter_id
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun listMeterDayFlowToday(zp: ZoneParam): List<MeterDayFlow>?

    @Select("""<script>
        SELECT zf.firm_id AS firmId
, mtr.meter_id AS meterId
, mtr.meter_name AS meterName
, ST_AsText(mtr.meter_loc) AS meterLoc
, meter_time AS `time`
, meter_flow AS flow
, flow_measure AS measureFlow
, pressure_avg AS pressureAvg
, flow_min AS flowMin
, flow_max AS flowMax
, pressure_min AS pressureMin
, pressure_max AS pressureMax
, pressure_reading_min AS pressureReadingMin
, pressure_reading_max AS pressureReadingMax
, flow_hist AS histFlow
, flow_temp AS tempFlow
, meter_volume AS volume
, meter_duration AS duration
, start_reading AS startReading
, start_time AS startTime
, end_reading AS endReading
, end_time AS endTime
, pulse_liter AS pulseLiter

, yest_water AS yestWater
, `yest_min` AS yestMin
, `week_day` AS weekDay
, `week_avg` AS weekAvg
, `week_error` AS weekError
, `week_day_min` AS weekDayMin
, `week_min_avg` AS weekMinAvg
, `week_min_error` AS weekMinError
, `month_day` AS monthDay
, `month_avg` AS monthAvg
, `month_error` AS monthError
, `month_day_min` AS monthDayMin
, `month_min_avg` AS monthMinAvg
, `month_min_error` AS monthMinError
, `year_day` AS yearDay
, `year_month_avg` AS yearMonthAvg
, `year_month_error` AS yearMonthError
, `year_month_min` AS yearMonthMin
, `year_month_min_avg` AS yearMonthMinAvg
, `year_month_min_error` AS yearMonthMinError
, `update_time` AS updateTime
        FROM bw.dm_meter_day_flow zf
        JOIN (
        SELECT meter_id AS meterId
        , MAX(meter_time) meterTime
        FROM bw.dm_meter_day_flow
        GROUP BY meter_id) zfm ON zf.meter_id = zfm.meterId AND zf.meter_time = DATE_SUB(zfm.meterTime, INTERVAL 1 DAY)
        LEFT JOIN bw.bw_meter mtr ON zf.meter_id = mtr.meter_id
       <where>
           zf.firm_id LIKE #{firmId}
           <if test="meterId != null">
               AND zf.meter_id = #{meterId}
           </if>
           <if test="time != null">
               AND meter_time = #{time}
           </if>
           <if test="time1 != null">
               AND meter_time &gt;= #{time1}
           </if>
           <if test="time2 != null">
               AND meter_time &lt; #{time2}
           </if>
       </where>
        </script>""")
    fun listMeterDayFlowYest(zp: ZoneParam): List<MeterDayFlow>?
}